<?

/**
* Fetch the list of suppliers
*
* @param  string    $COracle  
* @param  string    $srcDb 
* @param  string    $lstSupplier 
*
*/
function fetchLstSupplier(&$COracle,
                          &$lstSupplier,
                          &$srcDb){
                            
  $query  = "SELECT SUPPLIER_NAME ".
            "FROM ".$srcDb." ".
            "ORDER BY SUPPLIER_NAME ASC";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $lstSupplier = $result["SUPPLIER_NAME"];
}

/**
* fetch the list of category
*
* @param  string    $COracle  
* @param  string    $srcDb
* @param  string    $lstGroup
*
*/
function fetchLstCategory(&$COracle,
                          &$srcDb,
                          &$lstCategory){

  $query  = "SELECT CAT_NAME ".
            "FROM ".$srcDb." ".
            "ORDER BY CAT_NAME ASC";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $lstCategory = $result["CAT_NAME"];
}

/**
* fetch the list of group
*
* @param  string    $COracle  
* @param  string    $srcCategory
* @param  string    $srcDb
* @param  string    $lstGroup
*
*/
function fetchLstGroup(&$COracle,
                       &$srcCategory,
                       &$srcDb,
                       &$lstGroup){
  
  $query  = "SELECT GRP_ID ".
            "FROM ".$srcDb." ".
            "ORDER BY GRP_NAME ASC";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $query  = "SELECT GRP_NAME ".
            "FROM ".$srcDb." ".
            "WHERE CAT_NAME='".$srcCategory."' ".
            "ORDER BY GRP_NAME ASC";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $lstGroup = $result["GRP_NAME"];
}

/**
* fetch the list of models
*
* @param  string    $COracle  
* @param  string    $srcGroup
* @param  string    $srcDb
* @param  string    $lstModel  
*
*/
function fetchLstModel(&$COracle,
                       &$srcGroup,
                       &$srcDbGroup,
                       &$srcDbModel,
                       &$lstModel){

  $query  = "SELECT GRP_ID ".
            "FROM ".$srcDbGroup." ".
            "WHERE  GRP_NAME='".$srcGroup."' ";    
  
  $COracle->execFetchQuery($query,
                           $nrows,
                           $resultGID);
                                     
  $query  = "SELECT MODEL ".
            "FROM ".$srcDbModel." ".
            "WHERE  STATUS='"._ACTIVE."' 
                    AND IGROUP='".$resultGID["GRP_ID"][0]."' ".
            "ORDER BY MODEL_ID ASC";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $lstModel = $result["MODEL"];
}

/**
* fetch properties of MODEL
*
* @param  string    $COracle  
* @param  string    $srcDb  
* @param  string    $model        source of model name
* @param  string    $modelId  
* @param  string    $modelUnit  
*
*/ 
function fetchPropModel(&$COracle,
                        &$srcDb,
                        &$model,
                        &$modelId,
                        &$modelUnit){

  $query = "SELECT MODEL_ID,
                   UNIT ".
           "FROM ".$srcDb." ".
           "WHERE MODEL='".$model."' ";
  
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $modelId    = $result["MODEL_ID"][0];
  $modelUnit  = $result["UNIT"][0];
}

/**
* fetch rate of currency
*
* @param  string    $COracle  
* @param  string    $srcSymbol 
* @param  string    $srcDb  
* @param  integer   $theRate 
*
*/
function fetchRate(&$COracle,
                   &$srcSymbol,
                   &$srcDb,
                   &$theRate){

  $query  = "SELECT CRR_RATE ".
            "FROM ".$srcDb." ".
            "WHERE  CRR_SYMBOL='".$srcSymbol."' 
                    AND STATUS='"._ACTIVE."' ";
  
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $theRate = $result["CRR_RATE"][0];
}

/**
* Fetch list of currency
* 
* @param  string    $COracle   
* @param  string    $srcDb  
* @param  array     $lstCurrency 
*
*/
function fetchLstCurrency(&$COracle,
                          &$srcDb,
                          &$lstCurrency){
                            
  $query  = "SELECT CRR_SYMBOL ".
            "FROM ".$srcDb." ".
            "ORDER BY CRR_SYMBOL ASC ";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $lstCurrency = $result["CRR_SYMBOL"];
}

/**
* Fetch list of convertion
*
* @param  string    $COracle
* @param  string    $srcLevelD
* @param  string    $srcUnit
* @param  string    $srcDb  
* @param  string    $lstConvertion   
* 
*/
function fetchLstConvertion(&$COracle,
                            &$srcLevelD,
                            &$srcUnit,
                            &$srcDb,
                            &$lstConvertion){
                              
  $query  = "SELECT UNIT_TO ".
            "FROM ".$srcDb." ".
            "WHERE  LEVELD >= ".$srcLevelD." 
                    AND UNIT_NAME='".$srcUnit."' 
                    AND STATUS='"._ACTIVE."' ";
  
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $lstConvertion = $result["UNIT_TO"];
}

/**
* fetch the value of this convertion criteria
*
* @param  string    $COracle
* @param  string    $srcFrom
* @param  string    $srcTo
* @param  string    $srcDb
* @param  string    $qtyConvertion
*
*/
function fetchQtyConvertion(&$COracle,
                            &$srcFrom,
                            &$srcTo,
                            &$srcDb,
                            &$qtyConvertion){
                            
  $query  = "SELECT UNIT_QTY 
             FROM ".$srcDb." 
             WHERE  UNIT_NAME='".$srcFrom."' 
                    AND UNIT_TO='".$srcTo."' ";
                    
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $qtyConvertion = $result["UNIT_QTY"][0];
}   

/**
* fetch the ID of category by category name
*
* @param  string    $COracle
* @param  string    $nmCategory
* @param  string    $srcDb
* @param  string    $idCategory
*
*/
function fetchIdCategory(&$COracle,
                         &$nmCategory,
                         &$srcDb,
                         &$idCategory){
                        
  $query = "SELECT CAT_ID 
            FROM ".$srcDb." 
            WHERE  CAT_NAME='".$nmCategory."' ";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $idCategory = $result["CAT_ID"][0];
}

/**
* fetch the ID of group by group name
*
* @param  string    $COracle
* @param  string    $nmGroup
* @param  string    $srcDb
* @param  string    $idGroup
*
*/
function fetchIdGroup(&$COracle,
                      &$nmGroup,
                      &$srcDb,
                      &$idGroup){
                        
  $query = "SELECT GRP_ID 
            FROM ".$srcDb." 
            WHERE  GRP_NAME='".$nmGroup."' ";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $idGroup = $result["GRP_ID"][0];
}


/**
* fetch the last number of transaction
* in this day
*
* @param  string    $COracle
* @param  string    $thisDate
* @param  string    $srcDb
* @param  string    $lastNumber
*
*/
function fetchHdrPchOdrNo(&$COracle,
                          &$thisDate,
                          &$srcDb,
                          &$lastNumber){
                            
  $query  = "SELECT NO 
             FROM ".$srcDb."
             WHERE  TR_DATE=TO_DATE('".$thisDate."','dd-mm-YYYY') 
             ORDER BY NO DESC ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  /** 
  * Return the result incremented
  *
  */
  $lastNumber = $result["NO"][0] + 1;
}

/**
* fetch the list of valid transactions
*
* @param  string    $COracle
* @param  string    $condt      the condition
* @param  string    $srcDb
* @param  string    $lstResult
*
*/
function fetchLstTransaction(&$COracle,
                             $condt,
      	                     &$srcDb,
      	                     &$nResult,
      	                     &$lstResult){
      	                      
  $query  = "SELECT JOB_ID,
                    REFERENCE,
                    TR_DATE,
                    STATUS,
                    SUPPLIER,
                    CURRENCY,
                    RATE,
                    MEMO
             FROM ".$srcDb."
             ".$condt." 
             ORDER BY TR_DATE DESC ";
             
  $COracle->execFetchQuery($query,
                           $nResult,
                           $lstResult);
}

/**
* fetch list of header data
*
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $srcDb
* @param  integer   $nHeader
* @param  string    $lstHeader
*
*/
function fetchLstDataHeader(&$COracle,
                            &$jobId,
                            &$srcDb,
                            &$nHeader,
                            &$lstHeader){
                              
  $query  = "SELECT * 
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' ";
  
  $COracle->execFetchQuery($query,
                           $nHeader,
                           $lstHeader);
}

/**
* fetch list of Sub detail data
* 
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $srcDb
* @param  integer   $nSubDetail
* @param  string    $lstSubDetail
*
*/
function fetchLstDataSubDetail(&$COracle,
                               &$jobId,
                               &$srcDb,
                               &$nSubDetail,
                               &$lstSubDetail){
                                
  $query  = "SELECT * 
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' 
             ORDER BY NO ASC ";
             
  $COracle->execFetchQuery($query,
                           $nSubDetail,
                           $lstSubDetail);
}

/**
* fetch list of detail data
* 
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $srcDb
* @param  integer   $nDetail
* @param  string    $lstDetail
*
*/
function fetchLstDataDetail(&$COracle,
                            &$jobId,
                            &$srcDb,
                            &$nDetail,
                            &$lstDetail){
                              
  $query  = "SELECT * 
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' ";
             
  $COracle->execFetchQuery($query,
                           $nDetail,
                           $lstDetail);
}

/**
* Fetch category name by id
*
* @param  string    $COracle
* @param  integer   $idCategory
* @param  string    $srcDb
* @param  string    $nmCategory
*
*/
function fetchCatById(&$COracle,
  		                &$idCategory,
  		                &$srcDb,
  		                &$nmCategory){

  $query  = "SELECT CAT_NAME 
             FROM ".$srcDb." 
             WHERE  CAT_ID=".$idCategory." ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $nmCategory = $result["CAT_NAME"][0];
}

/**
* Fetch group name by id
*
* @param  string    $COracle
* @param  integer   $idGroup
* @param  string    $srcDb
* @param  string    $nmGroup
*
*/
function fetchGrpById(&$COracle,
  		                &$idGroup,
  		                &$srcDb,
  		                &$nmGroup){

  $query  = "SELECT GRP_NAME 
             FROM ".$srcDb." 
             WHERE  GRP_ID=".$idGroup." ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $nmGroup = $result["GRP_NAME"][0];
}
?>